using System;
using System.Collections.Generic;
using System.Text;
using NBusiness.CodeDom;
using System.Text.RegularExpressions;
using NBusiness.CodeDom.Compiler;
using NBusiness.ESharp.Compiler;
using System.IO;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;
using NBusiness.Utilities;
using System.IO.IsolatedStorage;
using System.Windows.Forms;
using NBusiness.Templates.Contracts;
using System.CodeDom;
using NBusiness.Query;
using NBusiness.Query.SqlServer;

namespace NBusiness.Templates.Sql
{
    [ResourceDescription("SqlScriptsDescription")]
    public class SqlScriptsTemplate : TemplateBase, ISchemaUpdater, IQueryHelper 
    {
        #region Constants

        #region Foreign Keys
        private const string dropForeignKey =
            "IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[{0}].[FK_{1}_{2}]') AND parent_object_id = OBJECT_ID(N'[{0}].[{1}]'))\r\n" +
            "ALTER TABLE [{0}].[{1}] DROP CONSTRAINT [FK_{1}_{2}]\r\n" +
            "GO\r\n";

        private const string createForeignKey =
            "ALTER TABLE [{0}].[{1}] WITH CHECK ADD CONSTRAINT [FK_{1}_{2}] FOREIGN KEY({3})\r\n" +
            "REFERENCES [{0}].[{2}] ({4})\r\n" +
            "GO\r\n"; 
        #endregion

        #region Tables
        private const string dropTable =
            "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND type in (N'U'))\r\n" +
            "DROP TABLE [{0}].[{1}]\r\n" +
            "GO\r\n";

        private const string createTable =
            "CREATE TABLE [{0}].[{1}](\r\n" +
            "{2}\r\n" +
            ")\r\n" +
            "GO\r\n"; 
        #endregion

        #region Primary Keys
        private const string createPrimaryKey =
            "ALTER TABLE [{0}].[{1}] ADD CONSTRAINT [PK_{1}] PRIMARY KEY CLUSTERED\r\n" +
            "( {2} ASC ) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]\r\n" +
            "GO\r\n"; 
        #endregion

        #region Sprocs

        private const string dropSproc =
            "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND type in (N'P', N'PC'))\r\n" +
            "DROP PROCEDURE [{0}].[{1}]\r\n" +
            "GO\r\n";

        private const string insertSproc =
            "CREATE PROCEDURE [{0}Insert]\r\n" +
            "\t{1}\r\n" +
            "AS\r\n" +
            "BEGIN\r\n" +
            "\t{2}\r\n" +
            "\tINSERT INTO [{3}]\r\n" +
            "\t({4})\r\n" +
            "\tVALUES\r\n" +
            "\t({5});\r\n\r\n" +
            "\t{6}\r\n" +
            "END\r\n" + 
            "GO\r\n";

        private const string updateSproc =
            "CREATE PROCEDURE [{0}Update]\r\n" +
            "\t{1}\r\n" +
            "AS\r\n" +
            "BEGIN\r\n" +
            "\tUPDATE [{0}] SET\r\n" +
            "\t\t{2}\r\n" +
            "\tWHERE {3};\r\n" +
            "END\r\n" +
            "GO\r\n";

        private const string deleteSproc =
            "CREATE PROCEDURE [{0}Delete]\r\n" +
            "\t{1}\r\n" +
            "AS\r\n" +
            "BEGIN\r\n" +
            "\tDELETE FROM [{2}] WHERE \r\n" +
            "\t\t{3}\r\n;" +
            "END\r\n" +
            "GO\r\n";

        private const string fetchSproc =
            "CREATE PROCEDURE [{0}Fetch]\r\n" +
            "\t{1}\r\n" +
            "AS\r\n" +
            "BEGIN\r\n" +
            "\tSELECT {2}\r\n" +
            "\tFROM [{0}]\r\n" +
            "\tWHERE {3};\r\n" +
            "END\r\n" +
            "GO\r\n";

        private const string fetchAllSproc =
            "CREATE PROCEDURE [{0}CollectionFetchAll]\r\n" + // {0} = entity.Name
            "\t@StartIndex int = 0,\r\n" +
            "\t@MaxRows int = -1,\r\n" +
            "\t@SortField varchar(35) = null,\r\n" +
            "\t@SortOrder int = 0,\r\n" +
            "\t@TotalRows int = 0 output\r\n" +
            "AS\r\n" +
            "BEGIN\r\n" +
            "\tSET NOCOUNT ON;\r\n" +
            "\tDECLARE @Temp TABLE\r\n" +
            "\t(\r\n" +
            "\t\tTempId int IDENTITY(1,1) PRIMARY KEY,\r\n" +
            "\t\t{1}\r\n" + // {1} = [Column] datatype(...)
            "\t)\r\n" +
            "\tIF @SortOrder = 2\r\n" +
            "\tBEGIN\r\n" +
            "\t\tINSERT INTO @Temp ({2})\r\n" + // {2} = [Column],[Column]
            "\t\tSELECT {2}\r\n" + // {2} = [Column,[Column]
            "\t\tFROM [{0}]\r\n" + // {3} = entity.Name
            "\t\tORDER BY\r\n" +
            "\t\tCASE @SortField\r\n" +
            "{3}" + // {3} = "\t\t\tWHEN 'Column' THEN Column\r\n"
            "\t\t\tELSE {4}\r\n" + // {4} = [Column],[Column] where Column.IsId
            "\t\tEND\r\n" +
            "\t\tDESC -- Order in descending order\r\n" +
            "\tEND\r\n" +
            "\tELSE\r\n" +
            "\tBEGIN\r\n" +
            "\t\tINSERT INTO @Temp ({2})\r\n" + // {2} = [Column,[Column]
            "\t\tSELECT {2}\r\n" +  // {2} = [Column],[Column]
            "\t\tFROM [{0}]\r\n" + // {3} = entity.Name
            "\t\tORDER BY\r\n" +
            "\t\tCASE @SortField\r\n" +
            "{3}" + // {3} = "\t\t\tWHEN 'Column' THEN Column\r\n"
            "\t\t\tELSE {4}\r\n" + // {4} = [Column],[Column] where Column.IsId
            "\t\tEND\r\n" +
            "\t\tASC -- Order in ascending order (the default)\r\n" +
            "\tEND\r\n" +
            "\t-- Get the total row count, used for paging.\r\n" +
            "\tSET @TotalRows = (SELECT COUNT(TempId) FROM @Temp);\r\n" +
            "\t-- If paging is specified\r\n" +
            "\tIF (@MaxRows > 0)\r\n" +
            "\tBEGIN\r\n" +
            "\t\tSELECT * FROM @Temp\r\n" +
            "\t\tWHERE TempId - 1 >= @StartIndex\r\n" +
            "\t\tAND TempId - 1 < @StartIndex + @MaxRows\r\n" +
            "\tEND\r\n" +
            "\t-- Else return it all!\r\n" +
            "\tELSE SELECT * FROM @Temp;\r\n" +
            "END\r\n" +
            "GO\r\n";

        #region Fetch All Sibling Sproc
        private const string fetchAllSiblingSproc =
            "CREATE PROCEDURE [{0}FetchAll]\r\n" + // {0} = entity.Name
            "\t@StartIndex int = 0,\r\n" +
            "\t@MaxRows int = -1,\r\n" +
            "\t@SortField varchar(35) = null,\r\n" +
            "\t@SortOrder int = 0,\r\n" +
            "\t@TotalRows int = 0 output,\r\n" +
            "\t{1}\r\n" +           // {1} = [@Param type],
            "AS\r\n" +
            "BEGIN\r\n" +
            "\tSET NOCOUNT ON;\r\n" +
            "\t\tSELECT {2}\r\n" + // {2} = [Column],[Column]
            "\t\tFROM [{3}] AS t1 INNER JOIN [{4}] as t2\r\n" + // {3} = entity.Name {4}=left.Name + right.Name
            "\t\tON {5}\r\n" + //{5} = t1.[Column]=t2.[Column]
            "\t\tWHERE {6}\r\n;" + // {6} = t2.[Column]=@Param, t2.[Column]=@Param
            "\tEND\r\n" +
            "GO\r\n"; 
        #endregion

        #region Fetch By Child Relationship Sproc
        private const string fetchByChildRelationshipSproc =
            "CREATE PROCEDURE [{0}CollectionFetchBy{1}]\r\n" + // {0} = with.Name, {1} = from.Name
            "\t{2},\r\n" + // {2} = [Column] datatype(...), where column.IsId
            "\t@StartIndex int = 0,\r\n" +
            "\t@MaxRows int = -1,\r\n" +
            "\t@SortField varchar(35) = null,\r\n" +
            "\t@SortOrder int = 0,\r\n" +
            "\t@TotalRows int = 0 output\r\n" +
            "AS\r\n" +
            "BEGIN\r\n" +
            "\tSET NOCOUNT ON;\r\n" +
            "\tDECLARE @Temp TABLE\r\n" +
            "\t(\r\n" +
            "\t\tTempId int IDENTITY(1,1) PRIMARY KEY,\r\n" +
            "\t\t{3}\r\n" + // {3} = [Column] datatype(...)
            "\t)\r\n" +
            "\tIF @SortOrder = 2\r\n" +
            "\tBEGIN\r\n" +
            "\t\tINSERT INTO @Temp ({4})\r\n" + // {4} = [Column],[Column]
            "\t\tSELECT {4}\r\n" + // {4} = [Column],[Column]
            "\t\tFROM [{0}]\r\n" + // {0} = with.Name
            "\t\tWHERE {5}\r\n" + //{5} = [Column]=@Column, [Column]=@Column... where column.IsId
            "\t\tORDER BY\r\n" +
            "\t\tCASE @SortField\r\n" +
            "{6}" + // {6} = "\t\t\tWHEN 'Column' THEN Column\r\n"
            "\t\t\tELSE {7}\r\n" + // {7} = [Column],[Column] where Column.IsId
            "\t\tEND\r\n" +
            "\t\tDESC -- Order in descending order\r\n" +
            "\tEND\r\n" +
            "\tELSE\r\n" +
            "\tBEGIN\r\n" +
            "\t\tINSERT INTO @Temp ({4})\r\n" + // {4} = [Column],[Column]
            "\t\tSELECT {4}\r\n" + // {4} = [Column],[Column]
            "\t\tFROM [{0}]\r\n" + // {5} = with.Name
            "\t\tWHERE {5}\r\n" + //{5} = [Column]=@Column, [Column]=@Column... where column.IsId
            "\t\tORDER BY\r\n" +
            "\t\tCASE @SortField\r\n" +
            "{6}" + // {6} = "\t\t\tWHEN 'Column' THEN Column\r\n"
            "\t\t\tELSE {7}\r\n" + // {7} = [Column],[Column] where Column.IsId
            "\t\tEND\r\n" +
            "\t\tASC -- Order in ascending order (the default)\r\n" +
            "\tEND\r\n" +
            "\t-- Get the total row count, used for paging.\r\n" +
            "\tSET @TotalRows = (SELECT COUNT(TempId) FROM @Temp);\r\n" +
            "\t-- If paging is specified\r\n" +
            "\tIF (@MaxRows > 0)\r\n" +
            "\tBEGIN\r\n" +
            "\t\tSELECT * FROM @Temp\r\n" +
            "\t\tWHERE TempId - 1 >= @StartIndex\r\n" +
            "\t\tAND TempId - 1 < @StartIndex + @MaxRows\r\n" +
            "\tEND\r\n" +
            "\t-- Else return it all!\r\n" +
            "\tELSE SELECT * FROM @Temp;\r\n" +
            "END\r\n" +
            "GO\r\n";
        #endregion
        #endregion

        #endregion

        #region Member fields and properties
        private Dictionary<Type, string> _fieldTypes = new Dictionary<Type, string>();
        protected Dictionary<Type, string> FieldTypes
        {
            get { return _fieldTypes; }
        }
        #endregion

        #region Constructors
        public SqlScriptsTemplate()
		{
            LoadFieldTypeMap(_fieldTypes);
        }

        protected virtual void LoadFieldTypeMap(Dictionary<Type, string> fieldTypes)
        {
			fieldTypes.Add(typeof(int), "int");
            fieldTypes.Add(typeof(int?), "int");
			fieldTypes.Add(typeof(string), "text");
			fieldTypes.Add(typeof(Guid), "uniqueidentifier");
            fieldTypes.Add(typeof(Guid?), "uniqueidentifier");
			fieldTypes.Add(typeof(byte[]), "image");
			fieldTypes.Add(typeof(short), "smallint");
            fieldTypes.Add(typeof(short?), "smallint");
			fieldTypes.Add(typeof(long), "bigint");
            fieldTypes.Add(typeof(long?), "bigint");
			fieldTypes.Add(typeof(decimal), "money");
            fieldTypes.Add(typeof(decimal?), "money");
			fieldTypes.Add(typeof(double), "float");
            fieldTypes.Add(typeof(double?), "float");
			fieldTypes.Add(typeof(float), "real");
            fieldTypes.Add(typeof(float?), "real");
			fieldTypes.Add(typeof(DateTime), "datetime");
            fieldTypes.Add(typeof(DateTime?), "datetime");
			fieldTypes.Add(typeof(bool), "bit");
            fieldTypes.Add(typeof(bool?), "bit");
            fieldTypes.Add(typeof(object), "sql_variant");
		}
		#endregion

        #region Field Type Translations
        protected virtual string GetDataType(EntityField f, EntityRule[] rules)
        {
            string dataType = _fieldTypes[f.Type];
            if (dataType == "text")
            {
                List<EntityRule> relationship = new List<EntityRule>(rules);
                EntityValidation maxLength = (EntityValidation)relationship.Find(new Predicate<EntityRule>(delegate(EntityRule rule)
                {
                    bool success = false;
                    if(rule is EntityValidation)
                    {
                        EntityValidation vr = rule as EntityValidation;
                        success = vr.Validatable == f && vr.Type.Name == "maxlength";
                    }
                    return success;
                }));

                if (maxLength != null)
                {
                    dataType = "varchar(" + maxLength.Arguments[0] + ")";
                }
            }
            return dataType;
        }
        #endregion

        #region Generate
        public override EntityResourceFile[] Generate(PartialLanguage partialLanguage, params Entity[] entities)
        {
            List<EntityResourceFile> files = new List<EntityResourceFile>();
            if(entities.Length>0)
            {
                StringBuilder complete = new StringBuilder();
                string completeFileName = "Scripts\\complete.sql";

                complete.AppendLine("-- " + completeFileName + " - '" + this.GetType().FullName + "'");
                complete.AppendLine("-- This file was automatically generated by NBusiness");
                complete.AppendLine("-- on " + DateTime.Now.ToLongTimeString());

                foreach (Entity entity in entities)
                {
                    EntityResourceFile tableFile = new EntityResourceFile();
                    tableFile.FileName = "Scripts\\Tables\\" + entity.Fullname + "Table.sql";
                    tableFile.Action = EntityBuildAction.None;

                    StringBuilder tableSql = new StringBuilder();
                    tableSql.AppendLine("-- " + tableFile.FileName + " - '" + this.GetType().FullName + "'");
                    tableSql.AppendLine("-- This file was automatically generated by NBusiness");
                    tableSql.AppendLine("-- on " + DateTime.Now.ToLongTimeString());

                    string sql = CreateTable(entity);
                    complete.AppendLine(sql);

                    tableSql.AppendLine(sql);

                    tableFile.Data = Encoding.UTF8.GetBytes(tableSql.ToString());
                    files.Add(tableFile);
                }
                foreach(Entity entity in entities)
                {
                    EntityResourceFile keyFile = new EntityResourceFile();
                    keyFile.FileName = "Scripts\\Keys\\" + entity.Fullname + "Keys.sql";
                    keyFile.Action = EntityBuildAction.None;

                    StringBuilder keySql = new StringBuilder();
                    keySql.AppendLine("-- " + keyFile.FileName + " - '" + this.GetType().FullName + "'");
                    keySql.AppendLine("-- This file was automatically generated by NBusiness");
                    keySql.AppendLine("-- on " + DateTime.Now.ToLongTimeString());

                    string sql = CreateForeignKeys(entity);
                    complete.AppendLine(sql);

                    keySql.AppendLine(sql);

                    keyFile.Data = Encoding.UTF8.GetBytes(keySql.ToString());
                    files.Add(keyFile);
                }
                foreach(Entity entity in entities)
                {
                    EntityResourceFile sprocFile = new EntityResourceFile();
                    sprocFile.FileName = "Scripts\\Sprocs\\" + entity.Fullname + "Sprocs.sql";
                    sprocFile.Action = EntityBuildAction.None;

                    StringBuilder sprocSql = new StringBuilder();
                    sprocSql.AppendLine("-- " + sprocFile.FileName + " - '" + this.GetType().FullName + "'");
                    sprocSql.AppendLine("-- This file was automatically generated by NBusiness");
                    sprocSql.AppendLine("-- on " + DateTime.Now.ToLongTimeString());
                    
                    string sql = CreateProcedures(entity);
                    complete.AppendLine(sql);

                    sprocSql.AppendLine(sql);

                    sprocFile.Data = Encoding.UTF8.GetBytes(sprocSql.ToString());
                    files.Add(sprocFile);
                }

                Dictionary<Entity, Dictionary<Entity, EntityRelationship>> siblings = new Dictionary<Entity, Dictionary<Entity, EntityRelationship>>();
                foreach (Entity entity in entities)
                {
                    foreach (EntityRelationship r in entity.Relationships)
                    {
                        if (r.Type == EntityRelationshipType.Sibling &&
                            siblings.ContainsKey(r.With) && 
                            siblings[r.With].ContainsKey(entity))
                        {
                            EntityRelationship left = siblings[r.With][r.From];
                            EntityRelationship right = r;

                            #region Build table scripts
                            EntityResourceFile siblingTable = new EntityResourceFile();
                            siblingTable.FileName = "Scripts\\Tables\\" + left.Name + right.Name + "Table.sql";
                            siblingTable.Action = EntityBuildAction.None;

                            StringBuilder siblingTableSql = new StringBuilder();
                            siblingTableSql.AppendLine("-- " + siblingTable.FileName + " - '" + this.GetType().FullName + "'");
                            siblingTableSql.AppendLine("-- This file was automatically generated by NBusiness");
                            siblingTableSql.AppendLine("-- on " + DateTime.Now.ToLongTimeString());

                            string siblingSql = CreateSiblingTable(left, right);
                            complete.AppendLine(siblingSql);

                            siblingTableSql.AppendLine(siblingSql);

                            siblingTable.Data = Encoding.UTF8.GetBytes(siblingTableSql.ToString());
                            files.Add(siblingTable);
                            #endregion

                            #region Build fk scripts
                            EntityResourceFile siblingKeys = new EntityResourceFile();
                            siblingKeys.FileName = "Scripts\\Keys\\" + left.Name + right.Name + "Keys.sql";
                            siblingKeys.Action = EntityBuildAction.None;

                            StringBuilder siblingKeysSql = new StringBuilder();
                            siblingKeysSql.AppendLine("-- " + siblingKeys.FileName + " - '" + this.GetType().FullName + "'");
                            siblingKeysSql.AppendLine("-- This file was automatically generated by NBusiness");
                            siblingKeysSql.AppendLine("-- on " + DateTime.Now.ToLongTimeString());

                            siblingSql = CreateSiblingForeignKeys(left, right);
                            complete.AppendLine(siblingSql);
                            siblingKeysSql.AppendLine(siblingSql);

                            siblingKeys.Data = Encoding.UTF8.GetBytes(siblingKeysSql.ToString());
                            files.Add(siblingKeys); 
                            #endregion

                            #region Build stored procedures
                            EntityResourceFile siblingSprocs = new EntityResourceFile();
                            siblingSprocs.FileName = "Scripts\\Sprocs\\" + left.Name + right.Name + "Sprocs.sql";
                            siblingSprocs.Action = EntityBuildAction.None;

                            StringBuilder siblingSprocsSql = new StringBuilder();
                            siblingSprocsSql.AppendLine("-- " + siblingSprocs.FileName + " - '" + this.GetType().FullName + "'");
                            siblingSprocsSql.AppendLine("-- This file was automatically generated by NBusiness");
                            siblingSprocsSql.AppendLine("-- on " + DateTime.Now.ToLongTimeString());

                            siblingSql = CreateSiblingStoredProcedures(left, right);
                            complete.AppendLine(siblingSql);
                            siblingSprocsSql.AppendLine(siblingSql);

                            siblingSprocs.Data = Encoding.UTF8.GetBytes(siblingSprocsSql.ToString());
                            files.Add(siblingSprocs); 
                            #endregion
                        }
                        else if (r.Type == EntityRelationshipType.Sibling &&
                            r.From == entity)
                        {
                            if (!siblings.ContainsKey(r.From))
                            {
                                Dictionary<Entity, EntityRelationship> dictionary = new Dictionary<Entity, EntityRelationship>();
                                siblings.Add(r.From, new Dictionary<Entity, EntityRelationship>());
                            }
                            siblings[r.From].Add(r.With, r);
                        }
                    }
                }

                EntityResourceFile completeSql = new EntityResourceFile();
                completeSql.FileName = completeFileName;
                completeSql.Action = EntityBuildAction.None;
                completeSql.Data = Encoding.UTF8.GetBytes(complete.ToString());
                files.Add(completeSql);
            }
            return files.ToArray();
            
        }
        #endregion

        #region Create Drop Table
        protected virtual string CreateDropTable(string schema, string name)
        {
            return string.Format(dropTable, schema, name);
        }
        #endregion

        #region Create Drop Foreign Key
        protected virtual string CreateDropForeignKey(string schema, string from, string with)
        {
            return string.Format(dropForeignKey, schema, from, with);
        } 
        #endregion

        #region Create Table
        protected virtual string CreateTable(Entity entity)
        {
            StringBuilder sql = new StringBuilder();

            //Append the conditional drop relationships needed to be able to drop the table.
            foreach (EntityRelationship relationship in entity.Relationships)
            {
                if (relationship.Type == EntityRelationshipType.Sibling && relationship.From.Equals(entity))
                {
                    foreach (EntityRelationship back in relationship.With.Relationships)
                    {
                        if (back.With.Equals(entity))
                        {
                            sql.AppendLine(CreateDropForeignKey("dbo", relationship.Name + back.Name, relationship.From.Name));
                            sql.AppendLine(CreateDropForeignKey("dbo", back.Name + relationship.Name, relationship.From.Name));
                            break;
                        }
                    }
                }
                else
                {
                    sql.AppendLine(CreateDropForeignKey("dbo", relationship.From.Name, relationship.With.Name));
                }
            }

            //Append the conditional drop table.
            sql.AppendLine(CreateDropTable("dbo", entity.Name));

            StringBuilder columns = new StringBuilder();
            StringBuilder pks = new StringBuilder();

            //Build string for the columns
            foreach(EntityField f in entity.Fields)
            {
                if (columns.Length > 0) columns.Append(",\r\n");

                columns.Append("\t[" + f.Name + "] " + GetDataType(f, entity.Rules.ToArray()));
                
                //int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable
                if (f.IsAuto)
                {
                    if (f.Type != typeof(Guid))
                    {
                        columns.Append(" IDENTITY(1,1)");
                    }
                }

                if (!f.IsNullable) columns.Append(" NOT");
                columns.Append(" NULL");

                if (f.IsId) 
                {
                    if(pks.Length>0)pks.Append(", ");
                    pks.Append("[" + f.Name + "]");
                }                
            }
            //Append the create table
            sql.AppendLine(string.Format(createTable, "dbo", entity.Name, columns.ToString()));
    
            //Append the alter table to add the pk
            sql.Append(CreatePrimaryKey("dbo", entity.Name, pks.ToString()));

            return sql.ToString();
        } 
        #endregion

        #region Create Primary Key
        protected virtual string CreatePrimaryKey(string schema, string name, string columns)
        {
            return string.Format(createPrimaryKey, schema, name, columns);
        } 
        #endregion

        #region Create Foreign Keys
        protected virtual string CreateForeignKeys(Entity entity)
        {
            StringBuilder sql = new StringBuilder();
            foreach(EntityRelationship relationship in entity.Relationships)
            {
                if (relationship.With == entity && relationship.Type == EntityRelationshipType.Parent)
                {
                    sql.AppendLine(CreateDropForeignKey("dbo", relationship.From.Name, relationship.With.Name));

                    StringBuilder onKeys = new StringBuilder();
                    StringBuilder onValues = new StringBuilder();
                    foreach (EntityField f in relationship.On.Keys)
                    {
                        if (onKeys.Length > 0) onKeys.Append(", ");
                        onKeys.Append("[" + f.Name + "]");

                        if (onValues.Length > 0) onValues.Append(", ");
                        onValues.Append("[" + relationship.On[f].Name + "]");
                    }

                    sql.Append(string.Format(createForeignKey,
                        "dbo",
                        relationship.From.Name,
                        relationship.With.Name,
                        onKeys.ToString(),
                        onValues.ToString()));
                }
                
            }
            return sql.ToString();
        } 
        #endregion

        #region Sibling Relationships
        #region Create Sibling table
        protected virtual string CreateSiblingTable(EntityRelationship left, EntityRelationship right)
        {
            StringBuilder sql = new StringBuilder();

            //Create drop foreign keys
            sql.AppendLine(CreateDropForeignKey("dbo", left.From.Name, left.Name + right.Name));
            sql.AppendLine(CreateDropForeignKey("dbo", right.From.Name, left.Name + right.Name));

            //Create drop table
            sql.AppendLine(CreateDropTable("dbo", left.Name + right.Name));

            StringBuilder columns = new StringBuilder();
            StringBuilder keys = new StringBuilder();

            //Build string for the columns
            foreach (EntityField f in left.From.Fields)
            {
                if (f.IsId)
                {
                    if (columns.Length > 0) columns.Append(",\r\n");
                    columns.Append("\t[" + left.From.Name + f.Name + "] " + GetDataType(f, left.From.Rules.ToArray()) + " NOT NULL");

                    if (keys.Length > 0) keys.Append(",\r\n");
                    keys.Append("\t[" + left.From.Name + f.Name + "]");
                }
            }
            foreach (EntityField f in right.From.Fields)
            {
                if (f.IsId)
                {
                    if (columns.Length > 0) columns.Append(",\r\n");
                    columns.Append("\t[" + right.From.Name + f.Name + "] " + GetDataType(f, right.From.Rules.ToArray()) + " NOT NULL");

                    if (keys.Length > 0) keys.Append(",\r\n");
                    keys.Append("\t[" + right.From.Name + f.Name + "]");
                }
            }

            //Append the create table
            sql.AppendLine(string.Format(createTable, "dbo", left.Name + right.Name, columns.ToString()));

            //Append the alter table to add the pk
            sql.Append(CreatePrimaryKey("dbo", left.Name + right.Name, keys.ToString()));

            return sql.ToString();
        }

        protected virtual string CreateSiblingForeignKeys(EntityRelationship left, EntityRelationship right)
        {
            StringBuilder sql = new StringBuilder();

            foreach (EntityRelationship relationship in new EntityRelationship[] { left, right })
            {
                sql.AppendLine(CreateDropForeignKey("dbo", relationship.From.Name, left.Name + right.Name));

                StringBuilder pkFields = new StringBuilder();
                StringBuilder fkFields = new StringBuilder();
                foreach (EntityField f in relationship.From.Fields)
                {
                    if (f.IsId)
                    {
                        if (pkFields.Length > 0) pkFields.Append(", ");
                        pkFields.Append("[" + f.Name + "]");

                        if (fkFields.Length > 0) fkFields.Append(", ");
                        fkFields.Append("[" + relationship.From.Name + f.Name + "]");
                    }
                }

                sql.Append(string.Format(createForeignKey,
                    "dbo",
                    left.Name + right.Name,
                    relationship.From.Name,
                    fkFields.ToString(),
                    pkFields.ToString()));
            }
            return sql.ToString();
        }
        #endregion

        #region Create Sibling sprocs
        protected virtual string CreateSiblingStoredProcedures(EntityRelationship left, EntityRelationship right)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(CreateProcedureSiblingInsert(left, right));
            sql.Append(CreateProcedureSiblingDelete(left, right));
            sql.Append(CreateProcedureSiblingFetchAll(left, right));
            return sql.ToString();
        }

        protected virtual string CreateProcedureSiblingInsert(EntityRelationship left, EntityRelationship right)
        {
            StringBuilder parameters = new StringBuilder();
            StringBuilder columns = new StringBuilder();
            StringBuilder values = new StringBuilder();
            StringBuilder sql = new StringBuilder();

            foreach (EntityRelationship relationship in new EntityRelationship[] { left, right })
            {
                foreach (EntityField f in relationship.From.Fields)
                {
                    if (f.IsId)
                    {
                        if (parameters.Length > 0) parameters.Append(",\r\n\t");
                        parameters.Append("@" + f.Name + " " + GetDataType(f, relationship.From.Rules.ToArray()));

                        if (columns.Length > 0) columns.Append(", ");
                        columns.Append("[" + relationship.From.Name + f.Name + "]");

                        if (values.Length > 0) values.Append(", ");
                        values.Append("@" + f.Name);
                    }
                }
            }

            foreach (EntityRelationship relationship in new EntityRelationship[] { left, right })
            {
                sql.AppendLine(string.Format(dropSproc, "dbo", left.Name + right.Name + "Insert"));

                sql.AppendLine(string.Format(insertSproc,
                    left.Name + right.Name,
                    parameters.ToString(),
                    "",
                    left.Name + right.Name,
                    columns.ToString(),
                    values.ToString(),
                    ""));
            }
        
            return sql.ToString();
        }

        protected virtual string CreateProcedureSiblingDelete(EntityRelationship left, EntityRelationship right)
        {
            StringBuilder parameters = new StringBuilder();
            StringBuilder where = new StringBuilder();
            StringBuilder sql = new StringBuilder();

            foreach (EntityRelationship relationship in new EntityRelationship[] { left, right })
            {
                foreach (EntityField f in relationship.From.Fields)
                {
                    if (f.IsId)
                    {
                        if (parameters.Length > 0) parameters.Append(", ");
                        parameters.Append("@" + f.Name + " " + GetDataType(f, relationship.From.Rules.ToArray()));

                        if (where.Length > 0) where.Append("\r\n\tAND ");
                        where.Append("@" + f.Name + "=[" + relationship.From.Name + f.Name + "]");
                    }
                }
            }

            foreach (EntityRelationship relationship in new EntityRelationship[] { left, right })
            {
                sql.AppendLine(string.Format(dropSproc, "dbo", left.Name + right.Name + "Delete"));

                sql.AppendLine(string.Format(deleteSproc,
                    left.Name + right.Name,
                    parameters.ToString(),
                    left.Name + right.Name,
                    where.ToString()));
            }

            return sql.ToString();
        }

        protected virtual string CreateProcedureSiblingFetchAll(EntityRelationship left, EntityRelationship right)
        {
            StringBuilder sql = new StringBuilder();
            foreach (EntityRelationship relationship in new EntityRelationship[] { left, right })
            {
                sql.AppendLine(string.Format(dropSproc, "dbo", relationship.From.Name + relationship.With.Name + "FetchAll"));

                StringBuilder paramDeclarations = new StringBuilder();
                StringBuilder where = new StringBuilder();
                StringBuilder columns = new StringBuilder();
                StringBuilder joinOn = new StringBuilder();

                foreach (EntityField f in relationship.From.Fields)
                {
                    if (f.IsId)
                    {
                        string dataType = GetDataType(f, relationship.From.Rules.ToArray());

                        if (paramDeclarations.Length > 0) paramDeclarations.Append(",\r\n\t");
                        paramDeclarations.Append("@" + f.Name + " " + dataType);

                        if (where.Length > 0) where.Append(" AND ");
                        where.Append("@" + f.Name + "=t2.[" + relationship.From.Name + f.Name + "]");
                    }                    
                }

                foreach (EntityField f in relationship.With.Fields)
                {
                    if (f.IsId)
                    {
                        if (joinOn.Length > 0) joinOn.Append(", ");
                        joinOn.Append("t1.[" + f.Name + "]=t2.[" + relationship.With.Name + f.Name + "]");
                    }
                    if (columns.Length > 0) columns.Append(",");
                    columns.Append("[" + f.Name + "]");
                }

                sql.AppendLine(string.Format(fetchAllSiblingSproc,
                   relationship.From.Name + relationship.With.Name,
                   paramDeclarations.ToString(),
                   columns.ToString(),
                   relationship.With.Name,
                   left.Name + right.Name,
                   joinOn.ToString(),
                   where.ToString()));
            }
            return sql.ToString();
        }
        #endregion
        #endregion

        #region Create Sprocs
        protected virtual string CreateProcedures(Entity entity)
        {
            StringBuilder sprocs = new StringBuilder();
            sprocs.Append(CreateProcedureInsert(entity));
            sprocs.Append(CreateProcedureDelete(entity));
            sprocs.Append(CreateProcedureUpdate(entity));
            sprocs.Append(CreateProcedureFetch(entity));
            sprocs.Append(CreateProcedureFetchAll(entity));
            sprocs.Append(CreateProcedureFetchByRelationship(entity));
            return sprocs.ToString();
        }

        #region Insert
        protected virtual string CreateProcedureInsert(Entity entity)
        {
            StringBuilder parameters = new StringBuilder();
            StringBuilder initialize = new StringBuilder();
            StringBuilder columns = new StringBuilder();
            StringBuilder values = new StringBuilder();
            StringBuilder identitySet = new StringBuilder();

            foreach (EntityField f in entity.Fields)
            {
                if (f.IsId && f.IsAuto)
                {
                    if (parameters.Length > 0) parameters.Append(",\r\n\t");
                    parameters.Append("@" + f.Name + " " + GetDataType(f, entity.Rules.ToArray()) + " output");

                    if (f.Type != typeof(Guid))
                    {
                        identitySet.Append("SET @" + f.Name + "=@@IDENTITY;");
                    }
                    else
                    {
                        if (initialize.Length > 0) initialize.Append("\r\n\t");
                        initialize.Append("SET @" + f.Name + "=newid();");

                        if (columns.Length > 0) columns.Append(", ");
                        columns.Append("[" + f.Name + "]");

                        if (values.Length > 0) values.Append(", ");
                        values.Append("@" + f.Name);
                    }
                }
                else
                {
                    if (parameters.Length > 0) parameters.Append(",\r\n\t");
                    parameters.Append("@" + f.Name + " " + GetDataType(f, entity.Rules.ToArray()));

                    if (columns.Length > 0) columns.Append(", ");
                    columns.Append("[" + f.Name + "]");

                    if (values.Length > 0) values.Append(", ");
                    values.Append("@" + f.Name);
                }
            }

            StringBuilder sql = new StringBuilder();

            sql.AppendLine(string.Format(dropSproc, "dbo", entity.Name + "Insert"));

            sql.AppendLine(string.Format(insertSproc,
                entity.Name,
                parameters.ToString(),
                initialize.ToString(),
                entity.Name,
                columns.ToString(),
                values.ToString(),
                identitySet.ToString()));

            return sql.ToString();
        } 
        #endregion

        #region Delete
        protected virtual string CreateProcedureDelete(Entity entity)
        {
            StringBuilder parameters = new StringBuilder();
            StringBuilder where = new StringBuilder();

            foreach (EntityField f in entity.Fields)
            {
                if (f.IsId)
                {
                    if (parameters.Length > 0) parameters.Append(", ");
                    parameters.Append("@" + f.Name + " " + GetDataType(f, entity.Rules.ToArray()));

                    if (where.Length > 0) where.Append("\r\n\tAND ");
                    where.Append("@" + f.Name + "=[" + f.Name + "]");
                }
            }

            StringBuilder sql = new StringBuilder();

            sql.AppendLine(string.Format(dropSproc, "dbo", entity.Name + "Delete"));

            sql.AppendLine(string.Format(deleteSproc,
                entity.Name,
                parameters.ToString(),
                entity.Name,
                where.ToString()));

            return sql.ToString();
        } 
        #endregion

        #region Update
        protected virtual string CreateProcedureUpdate(Entity entity)
        {
            StringBuilder parameters = new StringBuilder();
            StringBuilder set = new StringBuilder();
            StringBuilder where = new StringBuilder();

            foreach (EntityField f in entity.Fields)
            {
                if (f.IsId)
                {
                    if (parameters.Length > 0) parameters.Append(",\r\n\t");
                    parameters.Append("@" + f.Name + " " + GetDataType(f, entity.Rules.ToArray()));

                    if (where.Length > 0) where.Append("\r\n\tAND");
                    where.Append("@" + f.Name + "=[" + f.Name + "]");
                }
                else
                {
                    if (parameters.Length > 0) parameters.Append(",\r\n\t");
                    parameters.Append("@" + f.Name + " " + GetDataType(f, entity.Rules.ToArray()));

                    if (set.Length > 0) set.Append(",\r\n\t\t");
                    set.Append("[" + f.Name + "]=@" + f.Name);
                }
            }

            StringBuilder sql = new StringBuilder();

            sql.AppendLine(string.Format(dropSproc, "dbo", entity.Name + "Update"));

            sql.AppendLine(string.Format(updateSproc,
                entity.Name,
                parameters.ToString(),
                set.ToString(),
                where.ToString()));

            return sql.ToString();
        } 
        #endregion

        #region Fetch
        protected virtual string CreateProcedureFetch(Entity entity)
        {
            StringBuilder parameters = new StringBuilder();
            StringBuilder columns = new StringBuilder();
            StringBuilder where = new StringBuilder();

            foreach (EntityField f in entity.Fields)
            {
                if (f.IsId)
                {
                    if (where.Length > 0) where.Append("\r\n\tAND ");
                    where.Append("[" + f.Name + "]=@" + f.Name);

                    if (parameters.Length > 0) parameters.Append(",\r\n\t");
                    parameters.Append("@" + f.Name + " " + GetDataType(f, entity.Rules.ToArray()));
                }

                if (columns.Length > 0) columns.Append(",\r\n\t\t");
                columns.Append("[" + f.Name + "]");
            }

            StringBuilder sql = new StringBuilder();

            sql.AppendLine(string.Format(dropSproc, "dbo", entity.Name + "Fetch"));

            sql.AppendLine(string.Format(fetchSproc,
                entity.Name,
                parameters.ToString(),
                columns.ToString(),
                where.ToString()));

            return sql.ToString();
        } 
        #endregion

        #region FetchAll
        protected virtual string CreateProcedureFetchAll(Entity entity)
        {
            // {1} = [Column] datatype(...)
            // {2} = [Column],[Column]
            // {3} = "\t\t\tWHEN 'Column' THEN Column\r\n"
            // {4} = [Column],[Column] where Column.IsId

            StringBuilder columnDeclarations = new StringBuilder();
            StringBuilder columns = new StringBuilder();
            StringBuilder sortWhen = new StringBuilder();
            StringBuilder sortElse = new StringBuilder();

            foreach (EntityField f in entity.Fields)
            {
                string dataType = GetDataType(f, entity.Rules.ToArray());

                if (columnDeclarations.Length > 0) columnDeclarations.Append(",\r\n\t\t");
                columnDeclarations.Append("[" + f.Name + "] " + dataType);

                if (columns.Length > 0) columns.Append(",");
                columns.Append("[" + f.Name + "]");

                if (dataType != "image" && dataType != "text")
                {
                    sortWhen.Append("\t\t\tWHEN '" + f.Name + "' THEN cast([" + f.Name + "] as sql_variant)\r\n");
                }

                if (f.IsId)
                {
                    if (sortElse.Length > 0) sortElse.Append(",");
                    sortElse.Append("[" + f.Name + "]");
                }
            }

            StringBuilder sql = new StringBuilder();

            sql.AppendLine(string.Format(dropSproc, "dbo", entity.Name + "CollectionFetchAll"));

            sql.AppendLine(string.Format(fetchAllSproc,
                entity.Name,
                columnDeclarations.ToString(),
                columns.ToString(),
                sortWhen.ToString(),
                sortElse.ToString()));

            return sql.ToString();
        } 
        #endregion

        #region FetchByRelationship
        protected virtual string CreateProcedureFetchByRelationship(Entity entity)
        {
            StringBuilder sql = new StringBuilder();
            foreach (EntityRelationship relationship in entity.Relationships)
            {
                switch (relationship.Type)
                {
                    case EntityRelationshipType.Child:
                        {
                            sql.AppendLine(CreateProcedureFetchByChildRelationship(relationship));
                            break;
                        }
                    case EntityRelationshipType.Sibling:
                        {
                            sql.AppendLine(CreateProcedureFetchBySiblingRelationship(relationship));
                            break;
                        }
                }
            }
            return sql.ToString();
        }

        protected virtual string CreateProcedureFetchByChildRelationship(EntityRelationship relationship)
        {
            // {0} = with.Name
            // {1} = from.Name
            // {2} = @Column datatype(...), where column.IsId
            // {3} = [Column] datatype(...)
            // {4} = [Column]=@Column, [Column]=@Column... where column.IsId
            // {5} = [Column],[Column]
            // {6} = "\t\t\tWHEN 'Column' THEN cast([Column] as sql_variant)\r\n"
            // {7} = [Column],[Column] where Column.IsId

            StringBuilder parameters = new StringBuilder();
            StringBuilder columnDeclarations = new StringBuilder();
            StringBuilder where = new StringBuilder();
            StringBuilder columns = new StringBuilder();
            StringBuilder sortWhen = new StringBuilder();
            StringBuilder sortElse = new StringBuilder();

            foreach (EntityField f in relationship.With.Fields)
            {
                string dataType = GetDataType(f, relationship.With.Rules.ToArray());

                if (columnDeclarations.Length > 0) columnDeclarations.Append(",\r\n\t\t");
                columnDeclarations.Append("[" + f.Name + "] " + dataType);

                if (columns.Length > 0) columns.Append(",");
                columns.Append("[" + f.Name + "]");

                if (dataType != "image" && dataType != "text")
                {
                    sortWhen.Append("\t\t\tWHEN '" + f.Name + "' THEN cast([" + f.Name + "] as sql_variant)\r\n");
                }

                if (f.IsId)
                {
                    if (sortElse.Length > 0) sortElse.Append(",");
                    sortElse.Append("cast([" + f.Name + "] as sql_variant)");                    
                }
            }
            foreach (EntityField f in relationship.On.Keys)
            {
                if (parameters.Length > 0) parameters.Append(",\r\n\t");
                parameters.Append("@" + f.Name + " " + GetDataType(f, relationship.With.Rules.ToArray()));

                if (where.Length > 0) where.Append("\r\n\t\tAND ");
                where.Append("[" + relationship.On[f].Name + "]=@" + f.Name);
            }

            StringBuilder sql = new StringBuilder();

            string with = relationship.With.Name;
            string from = relationship.From.Name;

            sql.AppendLine(string.Format(dropSproc, "dbo", with + "CollectionFetchBy" + from));

            sql.AppendLine(string.Format(fetchByChildRelationshipSproc,
                with,
                from,
                parameters.ToString(),
                columnDeclarations.ToString(),
                columns.ToString(), 
                where.ToString(),
                sortWhen.ToString(),
                sortElse.ToString()));

            return sql.ToString();
        }
        
        protected virtual string CreateProcedureFetchBySiblingRelationship(EntityRelationship relationship)
        {
            return null;
        }
        #endregion
        #endregion

        #region ISchemaUpdater Members

        public virtual void UpdateSchema(SchemaUpdaterOptions options, Entity[] entities)
        {
            OnProgress(0d, "Connecting...");
            foreach (EntityResourceFile file in Generate(PartialLanguage.CS, entities))
            {
                if (Path.GetFileName(file.FileName) == "complete.sql")
                {
                    string sql = Encoding.UTF8.GetString(file.Data);

                    string cn = (options as SqlScriptsOptions).GetConnectionString();
                    using (SqlConnection connection = new SqlConnection(cn))
                    {
                        connection.Open();
                        SqlTransaction transaction = (SqlTransaction)(connection.BeginTransaction());
                        try
                        {
                            string [] commands = sql.Split(new string[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
                            int completed = 0;
                            foreach (string command in commands)
                            {
                                string[] lines = command.Trim().Split('\n');
                                OnProgress((double)completed / (double)commands.Length, (lines.Length > 0 ? lines[0] : ""));
                                SqlCommand cmd = (SqlCommand)(connection.CreateCommand());
                                cmd.Transaction = transaction;
                                cmd.CommandText = command;
                                cmd.CommandType = CommandType.Text;
                                cmd.ExecuteNonQuery();
                                completed++;
                            }
                            transaction.Commit();
                        }
                        catch
                        {
                            transaction.Rollback();
                            throw;
                        }
                    }
                }
            }
            OnCompleted();
        }

        public virtual SchemaUpdaterOptions GetDefaultOptions()
        {
            return new SqlScriptsOptions();
        }

        public virtual SchemaOptionsEditResult EditOptions(SchemaUpdaterOptions options)
        {
            SchemaOptionsEditResult result = SchemaOptionsEditResult.Cancel;

            SqlScriptsConnectionWindow connect = new SqlScriptsConnectionWindow(options as SqlScriptsOptions);
            if (connect.ShowDialog() == DialogResult.OK)
            {
                result = SchemaOptionsEditResult.Continue;
            }

            return result;
        }

        public virtual string Name
        {
            get { return "SqlServer Schema Updater"; }
        }

        protected virtual void OnProgress(double progress, string message)
        {
            if (Progress != null) Progress(this, new SchemaUpdaterProgressArgs(progress, message));
        }
        
        public event EventHandler<SchemaUpdaterProgressArgs> Progress;

        protected virtual void OnCompleted()
        {
            if (Completed != null) Completed(this, EventArgs.Empty);
        }

        public event EventHandler Completed;

        #endregion

        #region IQueryHelper Members

        public System.CodeDom.CodeTypeMember[] GetHelpers(Entity entity)
        {
            List<CodeTypeMember> members = new List<CodeTypeMember>();
            CodeTypeConstructor constructor = new CodeTypeConstructor();
            
            CodeMemberField table = new CodeMemberField(typeof(SqlTable), "_table");
            table.Attributes = MemberAttributes.Private | MemberAttributes.Static;
            CodeFieldReferenceExpression tableReference = new CodeFieldReferenceExpression(null, "_table");

            CodeMemberProperty tableProp = new CodeMemberProperty();
            tableProp.Name = "Table";
            tableProp.Attributes = MemberAttributes.Public | MemberAttributes.Static;
            tableProp.Type = new CodeTypeReference(typeof(SqlTable));
            tableProp.GetStatements.Add(new CodeMethodReturnStatement(tableReference));

            members.Add(table);
            members.Add(tableProp);

            CodeAssignStatement tableAssign = new CodeAssignStatement(
                tableReference,
                new CodeObjectCreateExpression(
                    new CodeTypeReference(typeof(SqlTable)),
                    new CodePrimitiveExpression(entity.Name)));

            constructor.Statements.Add(tableAssign);
            members.Add(constructor);

            foreach (EntityField f in entity.Fields)
            {
                CodeMemberField field = new CodeMemberField(typeof(SqlColumn), "_" + StringUtil.ToCamelCase(f.Name));
                field.Attributes = MemberAttributes.Private | MemberAttributes.Static;
                CodeFieldReferenceExpression fieldReference = new CodeFieldReferenceExpression(null, "_" + StringUtil.ToCamelCase(f.Name));

                CodeMemberProperty fieldProp = new CodeMemberProperty();
                fieldProp.Name = f.Name;
                fieldProp.Attributes = MemberAttributes.Public | MemberAttributes.Static;
                fieldProp.Type = new CodeTypeReference(typeof(SqlColumn));
                fieldProp.GetStatements.Add(new CodeMethodReturnStatement(fieldReference));
                
                members.Add(fieldProp);
                members.Add(field);

                CodeAssignStatement fieldAssign = new CodeAssignStatement(
                    fieldReference,
                    new CodeObjectCreateExpression(
                        new CodeTypeReference(typeof(SqlColumn)),
                        new CodePrimitiveExpression(f.Name),
                        tableReference));

                constructor.Statements.Add(fieldAssign);
            }

            // Create a property that has each of the field names in an array.
            CodeMemberProperty p = new CodeMemberProperty();
            p.Type = new CodeTypeReference(typeof(SqlColumn[]));
            p.Name = "Fields";
            p.Attributes = MemberAttributes.Public | MemberAttributes.Static;

            CodeObjectCreateExpression fieldNamesInit = new CodeObjectCreateExpression(
                typeof(List<SqlColumn>));

            CodeVariableDeclarationStatement fieldNamesDeclaration =
                new CodeVariableDeclarationStatement(
                typeof(List<SqlColumn>),
                "_nameArray",
                fieldNamesInit);
            p.GetStatements.Add(fieldNamesDeclaration);

            CodeVariableReferenceExpression names = new CodeVariableReferenceExpression("_nameArray");
            foreach (EntityField f in entity.Fields)
            {
                CodeMethodReferenceExpression add = new CodeMethodReferenceExpression(
                    names,
                    "Add");
                CodeMethodInvokeExpression addName = new CodeMethodInvokeExpression(
                    add,
                    new CodeFieldReferenceExpression(null, "_" + StringUtil.ToCamelCase(f.Name)));

                p.GetStatements.Add(addName);
            }

            CodeMethodReferenceExpression toArray = new CodeMethodReferenceExpression(
                names,
                "ToArray");
            CodeMethodInvokeExpression toArrayInvoke = new CodeMethodInvokeExpression(toArray);
            CodeMethodReturnStatement returnArray = new CodeMethodReturnStatement(toArrayInvoke);

            p.GetStatements.Add(returnArray);
            members.Add(p);

            return members.ToArray();
        }

        #endregion
    }
}
